--CREATE DATABASE iManager
--go
--Use iManager

Create Table [User_Group]
(
	[user_group_id] int IDENTITY(1,1),
	[user_group_name] nvarchar(32) not null,
	[permission] varchar(32) not null,
	Primary Key ([user_group_id])
)
go
Create Table [Account]
(
	[user_id] int IDENTITY(1,1) not null,
	[username] varchar(32) not null,
	[password] nvarchar(32) not null,
	[user_group_id] int not null,
	[email] varchar(32),
	[first_name] nvarchar(32),
	[last_name] nvarchar(32),
	[phone] varchar(20),
	[status] bit Default 0,
	[date_added] DateTime Default GetDate(),
	Primary Key ([user_id]),
	Foreign Key ([user_group_id]) References [User_Group]([user_group_id])
)
go
Create Table [Customer_Group]
(
	[customer_group_id] int IDENTITY(1,1),
	[customer_group_name] nvarchar(32) not null,
	[customer_group_des] nvarchar(255),
	[status] bit,
	[date_start] datetime Default GetDate(),
	[date_end] datetime Default GetDate(),
	Primary Key ([customer_group_id])
)
go
Create Table [Customer]
(
	[customer_id] int IDENTITY(1,1),
	[first_name] nvarchar(32) not null,
	[last_name] nvarchar(32) not null,
	[email] nvarchar(32) not null,
	[address] nvarchar(32) not null,
	[city] nvarchar(32) not null,
	[phone] varchar(20) not null,
	[customer_group_id] int not null,
	[date_added] datetime Default GetDate(),
	Primary Key ([customer_id]),
	Foreign Key ([customer_group_id]) References [Customer_Group]([customer_group_id])
)
go
Create Table [Category]
(
	[category_id] int IDENTITY(1,1),
	[category_name] nvarchar(32) not null,
	[category_des] nvarchar(255),
	[image] varchar(32),
	[parent_id] int Default 0,
	[date_added] datetime Default GetDate(),
	[date_modified] datetime Default GetDate(),
	Primary Key ([category_id])
)
go
Create Table [Manufacturer]
(
	[manufacturer_id] int IDENTITY(1,1),
	[name] nvarchar(32) not null,
	[image] varchar(32),
	Primary Key ([manufacturer_id])
)
go
Create Table [Product]
(
	[product_id] int IDENTITY(1,1),
	[product_name] nvarchar(32) not null,
	[product_des] nvarchar(255),
	[product_quantity] int Default 0,
	[product_unit] nvarchar(32),
	[image] nvarchar(32),
	[manufacturer_id] int,
	[price] int,
	[date_available] datetime Default GetDate(),
	[status] bit,
	[date_added] datetime Default GetDate(),
	[date_modified] datetime Default GetDate(),
	Primary Key ([product_id]),
	Foreign Key ([manufacturer_id]) References [Manufacturer]([manufacturer_id])
)
go
Create Table [Product_Option]
(
	[product_option_id] int IDENTITY(1,1),
	[product_id] int not null,
	[product_option_des] nvarchar(255),
	Primary Key ([product_option_id]),
	Foreign Key ([product_id]) References [Product]([product_id])
)
go
Create Table [Product_Option_Value]
(
	[product_option_value_id] int IDENTITY(1,1),
	[product_option_id] int not null,
	[product_id] int not null,
	[quantity] int,
	[price] int,
	Primary Key ([product_option_value_id]),
	Foreign Key ([product_id]) References [Product]([product_id]),
	Foreign Key ([product_option_id]) References [Product_Option]([product_option_id])
)
go
Create Table [Product_Special]
(
	[product_special_id] int IDENTITY(1,1),
	[product_id] int not null,
	[category_id] int not null,
	[customer_group_id] int not null,
	[priority] int,
	[price] int,
	[date_start] datetime Default GetDate(),
	[date_end] datetime Default GetDate(),
	Primary Key ([product_special_id], [product_id]),
	Foreign Key ([category_id]) References [Category]([category_id]),
	Foreign Key ([customer_group_id]) References [Customer_Group]([customer_group_id])
)
go
Create Table [Product_To_Category]
(
	[product_id] int not null,
	[category_id] int not null,
	Primary Key ([product_id], [category_id]),
	Foreign Key ([product_id]) References [Product]([product_id]),
	Foreign Key ([category_id]) References [Category]([category_id])
)
go
Create Table [Coupon]
(
	[coupon_id] int IDENTITY(1,1),
	[coupon_name] nvarchar(32) not null,
	[coupon_des] nvarchar(255),
	[discount] int not null,
	[coupon_num] int not null,
	[date_start] datetime Default GetDate(),
	[date_end] datetime Default GetDate(),
	[date_added] datetime Default GetDate(),
	Primary Key ([coupon_id])	
)
go
Create Table [Coupon_To_Customer]
(
	[coupon_id] int not null,
	[customer_id] int not null,
	Primary Key ([coupon_id], [customer_id]),
	Foreign Key ([coupon_id]) References [Coupon]([coupon_id]),
	Foreign Key ([customer_id]) References [Customer]([customer_id])
)
go
Create Table [Coupon_To_Product]
(
	[coupon_id] int not null,
	[product_id] int not null,
	Primary Key ([coupon_id], [product_id]),
	Foreign Key ([coupon_id]) References [Coupon]([coupon_id]),
	Foreign Key ([product_id]) References [Product]([product_id])
)
go
Create Table [Currency]
(
	[currency_id] int IDENTITY(1,1),
	[title] nvarchar(32),
	[code] nvarchar(32),
	[symbol_left] nvarchar(32),
	[symbol_right] nvarchar(32),
	[status] bit,
	[date_modified] datetime Default GetDate(),
	Primary Key ([currency_id])
)
go
Create Table [Order_Status]
(
	[order_status_id] int IDENTITY(1,1),
	[name] nvarchar(32) not null,
	Primary Key ([order_status_id])
)
go
Create Table [Order]
(
	[order_id] int IDENTITY(1,1),
	[customer_id] int,
	[customer_group_id] int,
	[user_id] int not null,
	[total] int not null,
	[order_status_id] int,
	[currency_id] int not null,
	[coupon_id] int,
	[date_added] datetime Default GetDate(),
	[date_modified] datetime Default GetDate(),
	Primary Key ([order_id]),
	Foreign Key ([customer_id]) References [Customer]([customer_id]),
	Foreign Key ([customer_group_id]) References [Customer_Group]([customer_group_id]),
	Foreign Key ([user_id]) References [Account]([user_id]),
	Foreign Key ([order_status_id]) References [Order_Status]([order_status_id]),
	Foreign Key ([currency_id]) References [Currency]([currency_id]),
	Foreign Key ([coupon_id]) References [Coupon]([coupon_id])
)
go
Create Table [Order_Product]
(
	[order_product_id] int IDENTITY(1,1),
	[order_id] int not null,
	[product_id] int not null,
	[name] nvarchar(32) not null,
	[price] int not null,
	[quantity] int not null,
	[total] int not null,
	Primary Key ([order_product_id]),
	Foreign Key ([order_id]) References [Order]([order_id]),
	Foreign Key ([product_id]) References [Product]([product_id])
)